﻿

using System.Data;
using System.Data.SqlClient;

namespace ybx_caller.entity
{
    public  class GetDataFromHis
    {
        public static DataTable QueryDatabase(string b,string e)
        {
            string connectionString = "Server=192.168.130.168;Database=hisdbth;User Id=cisnew;Password=cis%$#@!;";
            string query = @"SELECT 
                            '420300' area_code,
                            'SYSTHYY' enterprise_id,
                            case type when '1' then '0' when '3' then '1' else '-' end check_type,
                            FORMAT(put_date, 'yyyy-MM-dd') service_date,
                            COUNT(1)  quantity
                            FROM  registration_data
                            WHERE  status = 'success'
                            AND put_date >= @b
                            AND put_date <= @e
                            GROUP BY FORMAT(put_date, 'yyyy-MM-dd'),type";
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);

                SqlParameter[] parameters = new SqlParameter[]
                {
                    new SqlParameter("@b", SqlDbType.VarChar) { Value = b+" 00:00:00" },
                    new SqlParameter("@e", SqlDbType.VarChar) { Value = e+" 23:59:59" }
                };
                command.Parameters.AddRange(parameters);
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    dataTable.Load(reader);
                }
            }
            return dataTable;
        }
    }
}
